"""
    Author:
    Date: 2019-01-07

    公会管理类
"""
import calendar
import datetime
import os

from apps.gma import helpers as gma_helpers
from entities import user_mgr
from utils import db, util

TABLE_NAME = 'cmf_guilds'


def fetch_guild_archive_file(guild_id, batch):
    """ 获取公会的结算文件 """
    file_path = f'./static/temp/guild_{guild_id}_{batch}.csv'
    if not os.path.exists(file_path):
        lines = []
        fields = ['日期', '批次', '公会id', '用户id', '用户昵称', '趣票', '在线时长', '通话时长', '通话次数', '接通率']
        lines.append(','.join(fields))
        lines.append('\n')

        records = get_guild_archive_detail(guild_id, batch)
        for r in records:
            archive_time = util.datetimetostr(r['archive_time'])
            batch = str(r['batch'])
            guild_id = str(r['guild_id'])
            uid = str(r['uid'])
            nickname = r['nickname']
            archive_ticket = str(r['archive_ticket'])
            online_seconds = str(r['online_seconds'])
            call_seconds = str(r['call_seconds'])
            call_count = str(r['call_count'])
            receiving_rate = str(r['receiving_rate'])
            fields = [archive_time, batch, guild_id, uid, nickname, archive_ticket, online_seconds, call_seconds, call_count, f'{receiving_rate}%']
            lines.append(','.join(fields))
            lines.append('\n')

        f = open(file_path, 'w')
        f.writelines(lines)
        f.close()

    return file_path


def get_guild_daily_stat_data(guild_id, days):
    """ 获取公会的天统计数据 """
    stat_data = []
    begin = datetime.date.today() + datetime.timedelta(days=1)
    for i in range(days):
        end = begin
        begin = begin - datetime.timedelta(days=1)
        batch = '%4d-%02d-%02d' % (begin.year, begin.month, begin.day)
        begin_time = util.datetimetostr(begin)
        end_time = util.datetimetostr(end)
        total_online_seconds = get_guild_total_online_seconds(guild_id, begin_time, end_time)
        total_call_seconds = get_guild_total_call_seconds(guild_id, begin_time, end_time)
        total_income = get_guild_total_income(guild_id, begin_time, end_time)
        data = {
            'batch': batch,
            'begin_time': begin_time,
            'end_time': end_time,
            'total_online_seconds': total_online_seconds,
            'total_call_seconds': total_call_seconds,
            'total_income': total_income,
        }
        stat_data.append(data)
    return stat_data


def get_guild_weekly_stat_data(guild_id, weeks):
    """ 获取公会的周统计数据 """
    stat_data = []
    begin = util.get_last_weekday(calendar.MONDAY)
    for i in range(weeks):
        end = begin + datetime.timedelta(days=7)
        batch = '%4d年%02d周' % (end.year, begin.isocalendar()[1])
        begin_time = util.datetimetostr(begin)
        end_time = util.datetimetostr(end)
        total_online_seconds = get_guild_total_online_seconds(guild_id, begin_time, end_time)
        total_call_seconds = get_guild_total_call_seconds(guild_id, begin_time, end_time)
        total_income = get_guild_total_income(guild_id, begin_time, end_time)
        data = {
            'batch': batch,
            'begin_time': begin_time,
            'end_time': end_time,
            'total_online_seconds': total_online_seconds,
            'total_call_seconds': total_call_seconds,
            'total_income': total_income,
        }
        stat_data.append(data)
        begin = begin - datetime.timedelta(days=7)
    return stat_data


def get_guild_monthly_stat_data(guild_id, months):
    """ 获取公会的月统计数据 """
    stat_data = []
    begin = util.get_first_day_of_month(0)
    for i in range(months):
        end = begin
        begin = util.get_first_day_of_month(i + 1)
        batch = '%4d年%02d月' % (begin.year, begin.month)

        begin_time = util.datetimetostr(begin)
        end_time = util.datetimetostr(end)
        total_online_seconds = get_guild_total_online_seconds(guild_id, begin_time, end_time)
        total_call_seconds = get_guild_total_call_seconds(guild_id, begin_time, end_time)
        total_income = get_guild_total_income(guild_id, begin_time, end_time)
        data = {
            'batch': batch,
            'begin_time': begin_time,
            'end_time': end_time,
            'total_online_seconds': total_online_seconds,
            'total_call_seconds': total_call_seconds,
            'total_income': total_income,
        }
        stat_data.append(data)
        begin = begin - datetime.timedelta(days=1)
    return stat_data


def get_guild_list(request=None):
    """ 公会列表 """
    guild_condition = gma_helpers.build_guild_filter_condition_for_request(request)
    sql = f'''
        SELECT      *,
                    g.id AS guild_id,
                    g.name AS guild_name
        FROM        {TABLE_NAME} as g
        JOIN        cmf_users AS u ON g.chief_uid=u.id
        WHERE       {guild_condition} AND
                    g.deleted=0
        ORDER BY    g.id;
    '''
    data = db.query_data_from_db(sql)
    return [] if data is None else data


def get_guild_info(guild_id):
    """ 公会详情 """
    sql = f'''
        SELECT      *,
                    g.id AS guild_id,
                    g.name AS guild_name
        FROM        {TABLE_NAME} as g
        JOIN        cmf_users AS u ON g.chief_uid=u.id
        WHERE       g.id={guild_id}
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    return None if data is None else data[0]


def get_guild_members(guild_id):
    """ 获取公会成员列表 """
    sql = f'''
        SELECT      *,
                    u.id AS uid,
                    u.is_busy AS is_busy,
                    u.set_busy_time AS set_busy_time
        FROM        cmf_guilds_members as m
        JOIN        cmf_users AS u ON m.uid=u.id
        WHERE       m.guild_id={guild_id};
    '''
    data = db.query_data_from_db(sql)
    return [] if data is None else data


def get_guild_by_name(guild_name):
    """ 根据公会名称查找公会 """
    sql = '''
        SELECT      *
        FROM        cmf_guilds
        WHERE       name=%(guild_name)s
        LIMIT       1;
    '''
    params = {
        'guild_name': guild_name,
    }
    data = db.query_data_from_db(sql, params)
    return data[0] if data else None


def get_guild_by_chief_uid(chief_uid):
    """ 根据公会会长的uid查找公会 """
    sql = f'''
        SELECT      *
        FROM        {TABLE_NAME}
        WHERE       chief_uid={chief_uid}
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    return data[0] if data else None


def get_guild_by_mobile(mobile):
    """ 根据手机号码查找公会 """
    sql = f'''
        SELECT      *
        FROM        {TABLE_NAME}
        WHERE       mobile=%(mobile)s
        LIMIT       1;
    '''
    params = {
        'mobile': mobile,
    }
    data = db.query_data_from_db(sql, params)
    return data[0] if data else None


def create_guild(guild_name, chief_uid):
    """ 创建公会 """
    sql = '''
        INSERT INTO cmf_guilds
        SET         name=%(guild_name)s,
                    chief_uid=%(chief_uid)s;
    '''
    params = {
        'guild_name': guild_name,
        'chief_uid': chief_uid,
    }
    affected_rows = db.execute_sql(sql, params)
    return affected_rows == 1


def get_guild_total_online_seconds(guild_id, begin_time, end_time):
    """ 获取公会在指定时间内的累计在线时长 """
    settled_seconds = get_guild_settled_online_seconds(guild_id, begin_time, end_time)
    unsettled_seconds = get_guild_unsettled_online_seconds(guild_id, begin_time, end_time)
    return settled_seconds + unsettled_seconds


def get_guild_unsettled_online_seconds(guild_id, begin_time, end_time):
    """ 获取公会在指定时间内的未结算在线时长 """
    begin_ts = util.strtotime(begin_time)
    end_ts = util.strtotime(end_time)
    seconds = 0
    members = get_guild_members(guild_id)
    for member in members:
        uid = member['uid']
        set_busy_ts = util.datetimetotime(member['set_busy_time'])
        if begin_ts < set_busy_ts < end_ts:
            seconds += user_mgr.get_broadcaster_total_unsettled_online_seconds(uid)
    return seconds


def get_guild_settled_online_seconds(guild_id, begin_time, end_time):
    """ 获取公会在指定时间内的结算在线时长 """
    sql = '''
        SELECT      *
        FROM        cmf_users_online_log AS o
        JOIN        cmf_guilds_members AS m ON o.uid=m.uid
        WHERE       m.guild_id=%(guild_id)s AND
                    o.end_time>=%(begin_time)s AND
                    o.begin_time<=%(end_time)s;
    '''
    params = {
        'guild_id': guild_id,
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    if not data:
        return 0

    begin_time = util.strtotime(begin_time)
    end_time = util.strtotime(end_time)
    total_seconds = 0
    for record in data:
        record_begin_time = util.datetimetotime(record['begin_time'])
        record_end_time = util.datetimetotime(record['end_time'])
        total_seconds += record['online_seconds']

        if record_begin_time < begin_time:
            total_seconds -= begin_time - record_begin_time
        if record_end_time > end_time:
            total_seconds -= record_end_time - end_time
    return total_seconds


def get_guild_total_call_seconds(guild_id, begin_time, end_time):
    """ 获取公会在指定时间内的累计通话时长 """
    caller_seconds = get_guild_total_call_seconds_as_caller(guild_id, begin_time, end_time)
    callee_seconds = get_guild_total_call_seconds_as_callee(guild_id, begin_time, end_time)
    return caller_seconds + callee_seconds


def get_guild_total_call_seconds_as_caller(guild_id, begin_time, end_time):
    """ 获取公会在指定时间内的累计拨出时长 """
    sql = '''
        SELECT      *
        FROM        cmf_vchat_call_log AS c
        JOIN        cmf_guilds_members AS m ON c.caller_uid=m.uid
        WHERE       m.guild_id=%(guild_id)s AND
                    c.end_time>=%(begin_time)s AND
                    c.begin_time<=%(end_time)s;
    '''
    params = {
        'guild_id': guild_id,
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    if not data:
        return 0

    begin_time = util.strtotime(begin_time)
    end_time = util.strtotime(end_time)
    total_seconds = 0
    for record in data:
        record_begin_time = util.datetimetotime(record['begin_time'])
        record_end_time = util.datetimetotime(record['end_time'])
        total_seconds += record['chat_seconds']

        if record_begin_time < begin_time:
            total_seconds -= begin_time - record_begin_time
        if record_end_time > end_time:
            total_seconds -= record_end_time - end_time
    return total_seconds


def get_guild_total_call_seconds_as_callee(guild_id, begin_time, end_time):
    """ 获取公会在指定时间内的累计接听时长 """
    sql = '''
        SELECT      *
        FROM        cmf_vchat_call_log AS c
        JOIN        cmf_guilds_members AS m ON c.callee_uid=m.uid
        WHERE       m.guild_id=%(guild_id)s AND
                    c.end_time>=%(begin_time)s AND
                    c.begin_time<=%(end_time)s;
    '''
    params = {
        'guild_id': guild_id,
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    if not data:
        return 0

    begin_time = util.strtotime(begin_time)
    end_time = util.strtotime(end_time)
    total_seconds = 0
    for record in data:
        record_begin_time = util.datetimetotime(record['begin_time'])
        record_end_time = util.datetimetotime(record['end_time'])
        total_seconds += record['chat_seconds']

        if record_begin_time < begin_time:
            total_seconds -= begin_time - record_begin_time
        if record_end_time > end_time:
            total_seconds -= record_end_time - end_time
    return total_seconds


def get_guild_total_income(guild_id, begin_time, end_time):
    """ 获取公会在指定时间内的累计收益 """
    sql = '''
        SELECT      IFNULL(SUM(amount), 0) AS amount
        FROM        cmf_users_consumption_log AS c
        JOIN        cmf_guilds_members AS m ON c.uid=m.uid
        WHERE       m.guild_id=%(guild_id)s AND
                    c.add_time>=%(begin_time)s AND
                    c.add_time<=%(end_time)s AND
                    ((c.type>1000 AND c.type<1100) OR (c.type>2000 AND c.type<2100) OR (c.type=1110));
    '''
    # c.amount > 0 AND
    params = {
        'guild_id': guild_id,
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['amount'] if data else 0


def get_guild_member_uid_list(guild_id):
    """ 获取公会的成员列表 """
    sql = f'''
        SELECT      *
        FROM        cmf_guilds_members AS m
        WHERE       m.guild_id={guild_id};
    '''
    data = db.query_data_from_db(sql)
    return [x['uid'] for x in data] if data else []


def get_guild_members_stat_data(guild_id, begin_time, end_time):
    """ 获取公会会员在指定时间内的统计数据 """
    member_uid_list = get_guild_member_uid_list(guild_id)
    stat_data = []
    for uid in member_uid_list:
        user = user_mgr.get_user_detail(uid)
        user['total_online_seconds'] = user_mgr.get_broadcaster_online_seconds(uid, begin_time, end_time)
        user['total_call_seconds'] = user_mgr.get_broadcaster_call_seconds(uid, begin_time, end_time)
        user['total_income'] = user_mgr.get_broadcaster_income(uid, begin_time, end_time)
        stat_data.append(user)
    return stat_data


def get_user_guild_id(uid):
    """ 获取用户的公会id """
    sql = f'''
        SELECT      *
        FROM        cmf_guilds_members as m
        WHERE       m.uid={uid};
    '''
    data = db.query_data_from_db(sql)
    return data[0]['guild_id'] if data else None


def add_guild_member(guild_id, uid):
    """ 添加公会成员 """
    sql = '''
        INSERT INTO cmf_guilds_members
        SET         guild_id=%(guild_id)s,
                    uid=%(uid)s;
    '''
    params = {
        'guild_id': guild_id,
        'uid': uid,
    }
    affected_rows = db.execute_sql(sql, params)
    return affected_rows == 1


def get_guild_archive_data(guild_id):
    """ 获取公会的归档数据 """
    sql = f'''
        SELECT      a.batch,
                    IFNULL(SUM(a.ticket), 0) AS total_tickets
        FROM        cmf_guilds_archive as a
        WHERE       a.guild_id={guild_id}
        GROUP BY    a.batch
        ORDER BY    a.batch DESC;
    '''
    data = db.query_data_from_db(sql)
    return data if data else None


def get_guild_archive_detail(guild_id, batch):
    """ 获取公会指定批次的结算详情 """
    sql = f'''
        SELECT      *,
                    a.ticket AS archive_ticket,
                    a.create_time AS archive_time
        FROM        cmf_guilds_archive AS a
        JOIN        cmf_users AS u ON a.uid=u.id
        WHERE       a.guild_id={guild_id} AND
                    a.batch={batch};
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def remove_guild_member(uid):
    """ 将用户从公会中移除 """
    sql = '''
        DELETE FROM cmf_guilds_members
        WHERE       uid=%(uid)s
        LIMIT       1;
    '''
    params = {
        'uid': str(uid),
    }
    affected_rows = db.execute_sql(sql, params)
    return affected_rows == 1


def bind_mobile(guild_id, mobile):
    """ 绑定公会号码 """
    sql = '''
        UPDATE      cmf_guilds
        SET         mobile=%(mobile)s
        WHERE       id=%(guild_id)s
        LIMIT       1;
    '''
    params = {
        'guild_id': str(guild_id),
        'mobile': mobile,
    }
    affected_rows = db.execute_sql(sql, params)
    return affected_rows == 1


def get_guild_archive_ticket(begin_time, end_time):
    """ 获取公会结算的趣票数量 """
    sql = '''
        SELECT      IFNULL(SUM(g.ticket), 0) AS amount
        FROM        cmf_guilds_archive AS g
        JOIN        cmf_users AS u ON g.uid=u.id
        WHERE       g.create_time>=%(begin_time)s AND
                    g.create_time<=%(end_time)s;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['amount'] if data else 0


def delete_guild(guild_id):
    """ 删除公会 """
    sql = '''
        UPDATE      cmf_guilds
        SET         deleted=1
        WHERE       id=%(guild_id)s AND deleted=0
        LIMIT       1;
    '''
    params = {
        'guild_id': str(guild_id),
    }
    affected_rows = db.execute_sql(sql, params)
    return affected_rows == 1


if __name__ == '__main__':
    print('ok')
